﻿using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.SqlClient;
using System.Web.Configuration;
using System.ComponentModel;
//using HospitalSystem.Core;
namespace HospitalSystem.Core
{
    public class recpromotedevelopmentLog
    {

        public void readtodatatable5(GridView GridView1, GridView GridView3, Label Label1, String actcode)
        {
            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection scon = new SqlConnection(constr);

            string actcode1 = actcode;
            scon.Open();
            DataTable dt = new DataTable();

            SqlCommand cmd = new SqlCommand("SELECT distinct a.[MaterialCode] as MaterialCode,b.Quantity as Note FROM [Material_skillType] a join [UsedMaterials] b  on a.MaterialCode=b.MaterialCode where b.PSNumber=@PSNumber and a.ACTCode=@actcode1", scon);
            cmd.Parameters.AddWithValue("@PSNumber", Label1.Text);
            cmd.Parameters.AddWithValue("@actcode1", actcode1);
            SqlDataReader reader8 = cmd.ExecuteReader();

            dt.Columns.Add("MaterialCode");
            dt.Columns.Add("Note");
            while (reader8.Read() != false)
            {
                dt.Rows.Add(reader8["MaterialCode"].ToString(), (reader8["Note"].ToString()));
                //x3.Add(reader8["ACTCode"].ToString());
                //x4.Add(reader8["Note"].ToString());

            }
            reader8.Close();


            for (int j = 0; j < dt.Rows.Count; j++)
            {
                string a = dt.Rows[j]["MaterialCode"].ToString();
                foreach (GridViewRow GR in GridView1.Rows)
                {
                    string b = GR.Cells[0].Text.ToString();
                    CheckBox chk = (CheckBox)GR.FindControl("CaseSelector4");
                    TextBox tb = (TextBox)GR.FindControl("TextBox3");
                    if (a == b)
                    {

                        tb.Text = dt.Rows[j]["Note"].ToString();
                        // tb.Text=
                        chk.Checked = true;
                    }
                }
            }
            GridView3.DataSource = dt;
            GridView3.DataBind();

            scon.Close();


        }



        public void BindDataMaterial(GridView GridView1, GridView GridView3, Label Label1, String actcode)
        {
            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection scon = new SqlConnection(constr);


            string actcode1 = actcode;
            scon.Open();
            SqlCommand cmd = new SqlCommand("SELECT distinct o.MaterialCode as MaterialCode,o.MaterialDesc as MaterialDesc FROM [Material] as O INNER JOIN [Material_skillType] as c ON o.MaterialCode=c.MaterialCode INNER JOIN [Activity] as oc ON c.ACTCode=oc.ACTCode where oc.ACTCode=@actcode1 order by o.MaterialDesc", scon);
            cmd.Parameters.AddWithValue("@actcode1", actcode1);
            SqlDataReader reader = cmd.ExecuteReader();

            GridView1.DataSource = reader;
            GridView1.DataBind();
            scon.Close();
            readtodatatable5(GridView1,GridView3,Label1,actcode);
        }


        public void BindDataService(GridView GridView2, String actcode)
        {
            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection scon = new SqlConnection(constr);
            string actcode1 = actcode;
            scon.Open();
            SqlCommand cmd = new SqlCommand("SELECT SACTCode,SACTDesc FROM SubActivity where ACTCode=@actcode1", scon);
            cmd.Parameters.AddWithValue("@actcode1", actcode1);
            SqlDataReader reader = cmd.ExecuteReader();

            GridView2.DataSource = reader;
            GridView2.DataBind();
            scon.Close();


        }


        public void insertdatatodb(Label SelectStat, GridView GridView2, Label Label1, GridView GridView11,String actcode)
        {
            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection scon = new SqlConnection(constr);
            SelectStat.Text = string.Empty;
            bool selectorChecked3 = false;

            DataTable dt2 = new DataTable();
            dt2.Columns.Add("SACTCode");

            dt2.Columns.Add("note");
            foreach (GridViewRow row in GridView2.Rows)
            {
                CheckBox cb = (CheckBox)row.FindControl("CaseSelector3");
                if (cb != null && cb.Checked)
                {
                    selectorChecked3 = true;
                    TextBox rb = row.FindControl("TextBox4") as TextBox;
                    DataRow dr2;
                    dr2 = dt2.NewRow();
                    string SACTCode = GridView2.Rows[row.RowIndex].Cells[0].Text;
                    // string CID = GridView2.Rows[row.RowIndex].Cells[1].Text;
                    SelectStat.Text += string.Format("{0} has been added <br />", SACTCode);
                    //  string CID = GridView1.Rows[row.RowIndex].Cells[1].Text;
                    string note = rb.Text;
                    dt2.Rows.Add(SACTCode, note);


                }
            }
            SelectStat.Visible = selectorChecked3;


            readtodatatable(GridView11, GridView2, Label1,actcode);
            deledata(GridView11,GridView2,Label1,actcode);

         
            for (int i = 0; i < dt2.Rows.Count; i++)
            {


                SqlCommand command = new SqlCommand("INSERT INTO [ServiceProvisionDetails] (ACTCode,PSNumber,Note) VALUES('" + dt2.Rows[i]["SACTCode"].ToString() + "',@PSNumber,'" + dt2.Rows[i]["note"].ToString() + "')", scon);
                scon.Open();
                command.Parameters.AddWithValue("@PSNumber", Label1.Text);
           
                command.ExecuteNonQuery();
                scon.Close();


            }


     //;
     readtodatatable(GridView11, GridView2, Label1,actcode);

        }








        public void insertdata2todb(Label SelectStat, GridView GridView1, GridView GridView3, Label Label1,String actcode)
        {
            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection scon = new SqlConnection(constr);

            SelectStat.Text = string.Empty;


            DataTable dt3 = new DataTable();
            dt3.Columns.Add("MaterialCode");


            dt3.Columns.Add("note");
            foreach (GridViewRow row in GridView1.Rows)
            {
                CheckBox cb2 = (CheckBox)row.FindControl("CaseSelector4");
                if (cb2 != null && cb2.Checked)
                {

                    TextBox rb2 = row.FindControl("TextBox3") as TextBox;
                    DataRow dr3;
                    dr3 = dt3.NewRow();
                    string MaterialCode = GridView1.Rows[row.RowIndex].Cells[0].Text;

                    SelectStat.Text += string.Format("{0} has been added <br />", MaterialCode);

                    string note = rb2.Text;
                    dt3.Rows.Add(MaterialCode, note);





                }
            }


            //   readtodatatable2();
           // deledata2(GridView1,GridView3);
            //readtodatatable(GridView11, GridView2, Label1);
            deledata2(GridView1, GridView3, Label1,actcode);

            string actcode1 =actcode;
            for (int i = 0; i < dt3.Rows.Count; i++)
            {


                SqlCommand command = new SqlCommand("INSERT INTO [UsedMaterials] (MaterialCode,PSNumber,Quantity,ACTCode) VALUES('" + dt3.Rows[i]["MaterialCode"].ToString() + "',@PSNumber,'" + dt3.Rows[i]["note"].ToString() + "',@actcode1)", scon);
                scon.Open();
                command.Parameters.AddWithValue("@PSNumber", Label1.Text);
                command.Parameters.AddWithValue("@actcode1", actcode1);
                command.ExecuteNonQuery();
                scon.Close();



                SqlCommand command2 = new SqlCommand("INSERT INTO [UsedMaterials_log] (MaterialCode,PSNumber,Quantity,ACTCode,logDateTime) VALUES('" + dt3.Rows[i]["MaterialCode"].ToString() + "',@PSNumber,'" + dt3.Rows[i]["note"].ToString() + "',@actcode1,@logDateTime)", scon);
                scon.Open();
                command2.Parameters.AddWithValue("@PSNumber", Label1.Text);
                command2.Parameters.AddWithValue("@actcode1", actcode1);
                command2.Parameters.AddWithValue("@logDateTime", DateTime.Now);
                command2.ExecuteNonQuery();
                scon.Close();


            }



          BindDataMaterial(GridView1,GridView3,Label1,actcode);
             //readtodatatable(GridView11, GridView2, Label1);
        }







        public void deledata(GridView GridView11, GridView GridView2, Label Label1,String actcode)
        {

            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection scon = new SqlConnection(constr);
            DataTable dt3 = new DataTable();
            dt3.Columns.Add("ACTCode");


            foreach (GridViewRow row in GridView11.Rows)
            {

                DataRow dr3;
                dr3 = dt3.NewRow();
                string ACTCode = GridView11.Rows[row.RowIndex].Cells[0].Text;

                dt3.Rows.Add(ACTCode);

            }

          


            for (int i = 0; i < dt3.Rows.Count; i++)
            {


                SqlCommand command = new SqlCommand("delete from [ServiceProvisionDetails] where  PSNumber=@PSNumber and ACTCode='" + dt3.Rows[i]["ACTCode"].ToString() + "'", scon);
                scon.Open();
                command.Parameters.AddWithValue("@PSNumber", Label1.Text);

                command.ExecuteNonQuery();
                scon.Close();


            }


          BindDataService(GridView2,actcode);

        }





        public void deledata2(GridView GridView1, GridView GridView3, Label Label1, String actcode)
        {
            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection scon = new SqlConnection(constr);

            DataTable dt3 = new DataTable();
            dt3.Columns.Add("MaterialCode");


            foreach (GridViewRow row in GridView3.Rows)
            {

                DataRow dr3;
                dr3 = dt3.NewRow();
                string MaterialCode = GridView3.Rows[row.RowIndex].Cells[0].Text;

                dt3.Rows.Add(MaterialCode);

            }


            string actcode1 = actcode;

            for (int i = 0; i < dt3.Rows.Count; i++)
            {


                SqlCommand command = new SqlCommand("delete from [UsedMaterials] where  PSNumber=@PSNumber and ACTCode=@actcode1 and MaterialCode='" + dt3.Rows[i]["MaterialCode"].ToString() + "'", scon);
                scon.Open();
                command.Parameters.AddWithValue("@PSNumber", Label1.Text);
                command.Parameters.AddWithValue("@actcode1", actcode1);
                command.ExecuteNonQuery();
                scon.Close();


            }

            BindDataMaterial(GridView1, GridView3, Label1,actcode);


        }












        public void readtodatatable(GridView GridView11, GridView GridView2, Label Label1,String actcode)
        {
            string constr = WebConfigurationManager.ConnectionStrings["strconnection"].ConnectionString;
            SqlConnection scon = new SqlConnection(constr);


            scon.Open();
            DataTable dt = new DataTable();
            string actcode1 = actcode;
            SqlCommand cmd = new SqlCommand("SELECT a.[SACTCode] as SACTCode,b.Note  FROM [SubActivity] a  join [ServiceProvisionDetails] b on a.SACTCode=b.ACTCode  where a.ACTCode=@actcode1 and b.PSNumber=@PSNumber", scon);
            cmd.Parameters.AddWithValue("@PSNumber", Label1.Text);
            cmd.Parameters.AddWithValue("@actcode1", actcode1);
            SqlDataReader reader8 = cmd.ExecuteReader();


            dt.Columns.Add("SACTCode");
            dt.Columns.Add("Note");
            while (reader8.Read() != false)
            {
                dt.Rows.Add(reader8["SACTCode"].ToString(), (reader8["Note"].ToString()));
                //x3.Add(reader8["ACTCode"].ToString());
                //x4.Add(reader8["Note"].ToString());

            }
            reader8.Close();


            for (int j = 0; j < dt.Rows.Count; j++)
            {
                string a = dt.Rows[j]["SACTCode"].ToString();
                foreach (GridViewRow GR in GridView2.Rows)
                {
                    string b = GR.Cells[0].Text.ToString();
                    CheckBox chk = (CheckBox)GR.FindControl("CaseSelector3");
                    TextBox tb = (TextBox)GR.FindControl("TextBox4");
                    if (a == b)
                    {

                        tb.Text = dt.Rows[j]["Note"].ToString();
                        // tb.Text=
                        chk.Checked = true;
                    }
                }
            }
            GridView11.DataSource = dt;
            GridView11.DataBind();

            scon.Close();


        }

    }
}